<?php

$root = $_SERVER['DOCUMENT_ROOT'];

include_once "$root/class/staff.php";
include_once "$root/class/roster.php";
/*
 * 1.inital roster,(finished)
 * 2.prepare the StartdutiesArray(finished)
 * 3.update roster (swop person)
 * 4.update duty name
 * 5.printout finial roster diagram(date/duty/people)
 */

/**
 * Description of admin
 *
 * @author markxi
 */
class admin extends staff {

    private $staffArray = array();
    public $roster;
    
    //private $DutyArray=array();
    //private $DateArray=array();

    //caculating fucntion variables
    //private $DutiesPoolArray = array();

    /* $dutiesPoolArray[]={
     *      [Duty1]=>array('mk1','mk2','mk3')
     *      [Duty2]=>array('mk2','mk3')
     * } 
     */

    //end of cacualting function variables

    /*
     * fist time to run the program
     */
    public function initialRosterTable($TempRosterArray, $date1, $date2) {
        $this->InitialRosterDutiesArray($TempRosterArray); //after admin first time setup duties array, create roster table
        $this->InitialSundayArray($date1, $date2);
        $TempSundaysArray = $this->getSundaysArray();
        $TempCount = count($TempRosterArray);
        $TempString = '';
        for ($i = 0; $i < $TempCount; $i++) {
            $TempString = $TempString . "`$TempRosterArray[$i]` varchar(30) NOT NULL, ";
        }
        //echo '<br>'.$TempString.'<br>';
        $sql1 = "DROP TABLE IF EXISTS `$dbName`.`roster`;";
        $sql2 = "CREATE TABLE `$dbName`.`roster` (`roster_id` int(10) NOT NULL,`Date` date NOT NULL,$TempString PRIMARY KEY (`roster_id`,`Date`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;";

        $this->DBTemp->DB->query($sql1);
        $this->DBTemp->DB->query($sql2);

        //--start insert earch 
        //fist identify how many duties are included.
        //prepare the duties list string
        $TempDutiesString = '';
        $TempDutiesInsertEmptyString = '';
        for ($i = 0; $i < $TempCount; $i++) {
            $TempDutiesString = $TempDutiesString . ",`$TempRosterArray[$i]`";
            $TempDutiesInsertEmptyString = $TempDutiesInsertEmptyString . ",''";
        }
        $TempSundaysArray1 = $this->roster->getSundaysArray();
        foreach ($TempSundaysArray1 as $value) {
            $sqlSundayInsert = "INSERT INTO `$dbName`.`roster` (`roster_id`, `Date` $TempDutiesString) VALUES (1, '$value'$TempDutiesInsertEmptyString);";
            $this->DBTemp->DB->query($sqlSundayInsert);
        }
        $this->createStaffDataTable();
    }

    /*
     * end of fist time
     */

    public function initial($Name, $PW) {
        $sqlTempUser1 = "DROP TABLE IF EXISTS `user`;";
        $this->DBTemp->DB->query($sqlTempUser1);
        $sqlTempUser2 = "CREATE TABLE `duties` (`id` int(10) NOT NULL AUTO_INCREMENT,`dutyName` varchar(50) NOT NULL,`description` varchar(500) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;";
        $this->DBTemp->DB->query($sqlTempUser2);
    }

    public function __construct($Email, $PW) {
        parent::__construct($Email, $PW);
        $this->roster = new roster();
    }

    public function setDutyArray($Array) {
        $this->roster->setDuitesArray($Array);
    }

    public function getDutyArray() {
        return $this->roster->getDutyArray();
    }

    public function caculateSundays() {
        $this->roster->caculateSundayArray();
    }

    public function setStaffArray($tempArray) {
        $this->staffArray = $tempArray;
    }

    public function getStaffArray() {
        return $this->staffArray;
    }

    public function fetchStaffArray() {
        $dbName = parent::getDBName();
        $sqlStaffList = "SELECT user_id,gName,surname,email FROM `roster_users`.`users` WHERE `rosterDBName` = '$dbName' AND `admin`=0";
        $this->DBTemp->DB->query($sqlStaffList);
        $result = $this->DBTemp->DB->get();
        $this->setStaffArray($result);
        return $result;
    }

    public function updateStaff() {
        
    }

    public function setRoster($newRoster) {
        $this->roster = $newRoster;
    }

    public function InitialRosterDutiesArray($DutiesArray) {
        $dbName = parent::getDBName();
        $sqlDuties1 = "DROP TABLE IF EXISTS `$dbName`.`duties`;";
        $this->DBTemp->DB->query($sqlDuties1);
        $sqlDuties2 = "CREATE TABLE `$dbName`.`duties` (`duty_id` int(10) NOT NULL AUTO_INCREMENT,`dutyName` varchar(50) NOT NULL,`description` varchar(500) NOT NULL,PRIMARY KEY (`duty_id`)) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;";
        $this->DBTemp->DB->query($sqlDuties2);
        foreach ($DutiesArray as $value) {
            $tempInsertDuties = "INSERT INTO `$dbName`.`duties` (`duty_id`, `dutyName`, `description`) VALUES (NULL, '$value','');";
            $this->DBTemp->DB->query($tempInsertDuties);
        }
    }

    public function InitialSundayArray($StartDate, $EndDate) {
        $this->roster->setStarEndDate($StartDate, $EndDate);
    }

    public function getSundaysArray() {
        return $this->roster->getSundaysArray();
    }

    //get all the roster duties from the db and then reset into the Array
    public function getRosterDutiesArray() {
        $sqlTemp = "SELECT `dutyName` FROM `duties`";
        $this->DBTemp->DB->query($sqlTemp);
        $result = $this->DBTemp->DB->get();
        $tempArray = array();
        foreach ($result as $value) {
            $tempArray[] = $value['dutyName'];
        }
        $this->roster->setDuitesArray($tempArray);
        return $this->roster->getDutyArray();
    }

    //call getRosterDutiesArray() above to get 
    public function showRosterDutiesArray() {
        $this->getRosterDutiesArray();
        $this->roster->showDutyArray();
    }

    public function checkpw($Email, $pw) {
        parent::checkPW($Email, $pw);
    }

    public function addStaff($GiveName, $Surname, $Email) {
        $emailUniqcheck = "SELECT `email` FROM `roster_users`.`users` WHERE `email` = '$Email'";
        $this->DBTemp->DB->query($emailUniqcheck);
        $result = $this->DBTemp->DB->get();
        if (count($result) == 0) {
            $TempSQLregistStaff = "INSERT INTO `roster_users`.`users` (`user_id`, `gName`, `surname`, `email`,`rosterDBName`) VALUES (NULL, '$GiveName', '$Surname','$Email','$this->dbName')";
            $this->DBTemp->DB->query($TempSQLregistStaff);
            return 'Create user successful';
        } else {
            return "Email $Email is already registed, please change another one";
        }
    }

    private function createStaffDataTable() {
        $DBName = parent::getDBName();
        $sqlStaffData = "DROP TABLE IF EXISTS `$DBName`.`staffData`";
        $this->DBTemp->DB->query($sqlStaffData);
        $sqlStaffData1 = "CREATE TABLE `$DBName`.`staffData` ( `roster_id` int(20) NOT NULL, `staff_id` int(20) NOT NULL, `dutiesArray` varchar(200) NOT NULL DEFAULT '', `exceptDayArray` varchar(200) NOT NULL DEFAULT '', PRIMARY KEY (`roster_id`,`staff_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1";
        $this->DBTemp->DB->query($sqlStaffData1);
    }

    public function fetchSundayArray() {
        $DBName = $this->getDBName();
        $sql = "select `Date` FROM `$DBName`.`roster` where `roster_id`=(select max(`roster_id`) from `$DBName`.`roster`) ";
        $this->DBTemp->DB->query($sql);
        $result = $this->DBTemp->DB->get();
        return $result;
    }

    public function fetchDutyArray() {
        $DBName = $this->getDBName();
        $sql = "select `dutyName` from `$DBName`.`duties`";
        $this->DBTemp->DB->query($sql);
        $result = $this->DBTemp->DB->get();
        return $result;
    }

    ///testing function
    public function TestingMaxId() {
        $DBName = parent::getDBName();
        $sql = "select `Date` FROM `$DBName`.`roster` where `roster_id`=(select max(`roster_id`) from `$DBName`.`roster`) ";
        $this->DBTemp->DB->query($sql);
        $result = $this->DBTemp->DB->get();
        echo "<pre>";
        print_r($result);
    }

    public function checkStaffInitial($Id) {
        $DBName = $this->getDBName();
        $sqlLastestRoster = "select `roster_id` FROM `$DBName`.`roster` where `roster_id`=(select max(`roster_id`) from `$DBName`.`roster`) ";
        $this->DBTemp->DB->query($sqlLastestRoster);
        $rosterIdArray = $this->DBTemp->DB->get();
        $rosterId = $rosterIdArray[0]['roster_id'];

        $sqlStaffLastestRoster = "select * from `$DBName`.`staffData` where `roster_id`=$rosterId and `staff_id`=$Id";
        $this->DBTemp->DB->query($sqlStaffLastestRoster);
        $result = $this->DBTemp->DB->get();

        if (count($result) == 0) {
            return FALSE;
        } else {
            return true;
        }
    }

    public function CreateRosterTable($date1, $date2) {
        $this->InitialSundayArray($date1, $date2);
        $TempSundaysArray = $this->getSundaysArray();
        $DutyArrayTemp = $this->fetchDutyArray();

        $array1 = array();
        foreach ($DutyArrayTemp as $value) {
            $array1[] = $value['dutyName'];
        }

        $rosterId = $this->getLatestRosterId() + 1;
        $dbName = $this->getDBName();
        $TempCount = count($array1);

        //--start insert earch 
        //fist identify how many duties are included.
        //prepare the duties list string
        $TempDutiesString = '';
        $TempDutiesInsertEmptyString = '';
        for ($i = 0; $i < $TempCount; $i++) {
            $TempDutiesString = $TempDutiesString . ",`$array1[$i]`";
            $TempDutiesInsertEmptyString = $TempDutiesInsertEmptyString . ",''";
        }
        foreach ($TempSundaysArray as $value) {
            $sqlSundayInsert = "INSERT INTO `$dbName`.`roster` (`roster_id`, `Date` $TempDutiesString) VALUES ($rosterId,'$value' $TempDutiesInsertEmptyString);";
            $this->DBTemp->DB->query($sqlSundayInsert);
        }
        return "create roster successful";
    }

    /*
     * below codes is for the roster genereation purpose
     * 1.get staff array-----staff[i][DutyArray]
     */

    function generatRosterArray() {
        //$random = rand(0, 10);
        //echo $random;
        
        //this is the array hold the final roster data;
        $rosterArray=array();
        
        $dutyArray = $this->fetchDutyArray();
        $dutyArrayCount = count($dutyArray);
        
        $sundayArray = $this->fetchSundayArray();
        $sundayArrayCount = count($sundayArray);
        
        $commitDuty = $this->fetchFilledStaffDataCommitDutyArray();
        $exceptDate = $this->fetchFilledStaffDataExcepDateArray();
        
        for($i=0;$i<$sundayArrayCount;$i++){
            $existArray=array();
            for($j=0;$j<$dutyArrayCount;$j++){
               $tempCand = $commitDuty[$dutyArray[$j]['dutyName']];
               $tempDate = $exceptDate[$sundayArray[$i]['Date']];
               if(count($tempCand)==0){
                $rosterArray[$i][$j]="";   
               }else if(count($tempCand)==1){
                  if(count($tempDate)==0){
                     if(!in_array($tempCand[0], $existArray)){
                      $rosterArray[$i][$j]=$tempCand[0];
                      $existArray[]=$tempCand[0];
                     }else{
                       $rosterArray[$i][$j]="";  
                     }
                  }else if(in_array($tempCand[0], $tempDate)){
                      $rosterArray[$i][$j]="";
                  }else{
                      if(!in_array($tempCand[0], $existArray)){
                      $rosterArray[$i][$j]=$tempCand[0];
                      $existArray[]=$tempCand[0];
                     }else{
                       $rosterArray[$i][$j]="";  
                     }
                  } 
                  
               }else {                 
                   $signal =0;
                   do{
                    $random = rand(0, count($tempCand)-1);
                       if(count($tempDate)==0){
                           if(!in_array($tempCand[$random], $existArray)){
                               $rosterArray[$i][$j]=$tempCand[$random];
                               $existArray[] = $tempCand[$random];
                               $signal=1;
                           }else{
                              $rosterArray[$i][$j]="";
                              $signal=1;
                           }
                       }else if(count($tempCand)==count($tempDate)){
                           $TempArray =$tempCand;
                           for($k=count($TempArray)-1;$k>=0;$k--){
                               if(in_array($TempArray[$k], $rosterArray)){
                                   unset($TempArray[$k]);
                               }
                           }
                           if(count($TempArray)>0){
                             shuffle($TempArray);
                             $random1 = rand(0, count($TempArray)-1);
                             if(!in_array($TempArray[$random1], $existArray)){
                               $rosterArray[$i][$j]=$TempArray[$random1];
                               $existArray[]=$TempArray[$random1];
                               $signal=1;
                             }else{
                               $rosterArray[$i][$j]="";
                               $signal=1;
                             }
                           }else{
                              $rosterArray[$i][$j]="";
                              $signal=1;
                           }
                       }else if(count($tempCand)< count($tempDate)){
                            $TempArray =$tempCand;
                           for($k=count($TempArray)-1;$k>=0;$k--){
                               if(in_array($TempArray[$k], $rosterArray)){
                                   unset($TempArray[$k]);
                               }
                           }
                           if(count($TempArray)>0){
                             shuffle($TempArray);
                             $random1 = rand(0, count($TempArray)-1);
                             if(!in_array($TempArray[$random1], $existArray)){
                                $rosterArray[$i][$j]=$TempArray[$random1];
                                $existArray[]=$TempArray[$random1];
                                $signal=1;
                             }else{
                               $rosterArray[$i][$j]=""; 
                               $signal=1;
                             }
                           }else{
                              $rosterArray[$i][$j]="";
                              $signal=1;
                           }
                       }else if(count($tempCand)>count($tempDate)){
                           $TempArray =$tempCand;
                           for($k=count($TempArray)-1;$k>=0;$k--){
                               if(in_array($TempArray[$k], $rosterArray)){
                                   unset($TempArray[$k]);
                               }
                           }
                           shuffle($TempArray);
                           $random2 = rand(0, count($TempArray)-1);
                           if(!in_array($TempArray[$random2], $existArray)){
                              $rosterArray[$i][$j]=$TempArray[$random2];
                              $existArray[]=$TempArray[$random2];
                              $signal=1;
                           }else{
                             $rosterArray[$i][$j]=""; 
                             $signal=1;
                           }
                       }
                   }while($signal==0);
                   
               }
               
            }
        }
        return $rosterArray;
    }
    
    function RosterWithEmail(){
      $DBName = parent::getDBName();
        $maxRosterId= $this->getLatestRosterId();
        $sql="SELECT B.email,B.user_id, B.surname, B.gName FROM `$DBName`.`staffData` as A cross join `roster_users`.`users` as B where A.roster_id = $maxRosterId and A.staff_id = B.user_id;";
        $this->DBTemp->DB->query($sql);
        $result = $this->DBTemp->DB->get();
        $tempArray =array();//$tempArray hold the decript code for array to their email;
        foreach ($result as $value) {
           $tempArray[$value['user_id']]=$value['email'];
        }
        //return $result;
        //return $tempArray;
        $rosterArrayTemp =  $this->generatRosterArray();
        $dutyCount =  count($this->fetchDutyArray());
        for($i=0;$i<count($rosterArrayTemp);$i++){
            for($j=0;$j<$dutyCount;$j++){
                if($rosterArrayTemp[$i][$j]!=""){
                   $rosterArrayTemp[$i][$j]=$tempArray[$rosterArrayTemp[$i][$j]]; 
                }
            }
        }
        //$this->echoArray($rosterArrayTemp);  
        return $rosterArrayTemp;
    }
    function RosterWithFullName(){
        $DBName = parent::getDBName();
        $maxRosterId= $this->getLatestRosterId();
        $sql="SELECT B.email,B.user_id, B.surname, B.gName FROM `$DBName`.`staffData` as A cross join `roster_users`.`users` as B where A.roster_id = $maxRosterId and A.staff_id = B.user_id;";
        $this->DBTemp->DB->query($sql);
        $result = $this->DBTemp->DB->get();
        $tempArray =array();//$tempArray hold the decript code for array to their email;
        foreach ($result as $value) {
           $tempArray[$value['user_id']]=$value['gName']." ".$value['surname'];
        }
        //return $result;
        //return $tempArray;
        $rosterArrayTemp =  $this->generatRosterArray();
        $dutyCount =  count($this->fetchDutyArray());
        for($i=0;$i<count($rosterArrayTemp);$i++){
            for($j=0;$j<$dutyCount;$j++){
                if($rosterArrayTemp[$i][$j]!=""){
                   $rosterArrayTemp[$i][$j]=$tempArray[$rosterArrayTemp[$i][$j]]; 
                }
            }
        }
        //$this->echoArray($rosterArrayTemp);  this is for testing array structure purpose
        return $rosterArrayTemp;
    }

    function genereateRoster() {
        $testday = $this->RosterWithFullName();
        $DutyArray = $this->fetchDutyArray();
        $SundayArray = $this->fetchSundayArray();
        $dutyTableHeadString = "<th>Date</th>";
        $SundayString = "";
        foreach ($DutyArray as $value) {
            $dutyTableHeadString = $dutyTableHeadString . "<th>" . $value["dutyName"] . "</th>";
        }
        $dutyTableHeadString = "<thead><tr>" . $dutyTableHeadString . "</tr></thead>";
        $lineArray = array();
        for ($k = 0; $k < count($SundayArray); $k++) {
            $emptyString = "";
            for ($i = 0; $i < count($DutyArray); $i++) {
                $emptyString = $emptyString . "<td>" . $testday[$k][$i] . "</td>";
            }
            $lineArray[$k] = $emptyString;
        }
        for ($j = 0; $j < count($SundayArray); $j++) {
            $SundayString = $SundayString . "<tr><td>" . $SundayArray[$j]["Date"] . "</td>" . $lineArray[$j] . "</tr>";
        }
        $SundayString = "<tbody>" . $SundayString . "</tbody>";
        $response = "<table cellspacing='2'>" . $dutyTableHeadString . $SundayString . "</table>";
        return $response;
    }
    
    function fetchFilledStaffDataExcepDateArray(){
        $exceptDateArray=array();
        $tempDateArray = $this->fetchSundayArray();
        //$tempDutyArray = $this->fetchDutyArray();
        
        $DBName = parent::getDBName();
        $id = $this->getLatestRosterId();
        $sql = "select * from `$DBName`.`staffData` where `roster_id`=$id";
        $this->DBTemp->DB->query($sql);
        $result = $this->DBTemp->DB->get();
        
        $temp=array();
        foreach ($result as $value) {
            if(unserialize($value['exceptDayArray'])!=NULL){
              $temp[$value['staff_id']] = unserialize($value['exceptDayArray']);  
            } 
        }
        
        foreach ($tempDateArray as $value) {
            $exceptDateArray[$value['Date']]=array();
            foreach ($temp as $key => $valueArray) {
            $tempId = $key;
            foreach ($valueArray as $excepDate) {
                    if($value['Date']==$excepDate){
                        $exceptDateArray[$value['Date']][]=$tempId;
                    }
                }
            }
        }
        
        //$this->echoArray($exceptDateArray); 
        return $exceptDateArray;
    }
    
    function fetchFilledStaffDataCommitDutyArray(){
        $commitDutyArray=array();
        //$tempDateArray = $this->fetchSundayArray();
        $tempDutyArray = $this->fetchDutyArray();
        
        $DBName = parent::getDBName();
        $id = $this->getLatestRosterId();
        $sql = "select * from `$DBName`.`staffData` where `roster_id`=$id";
        $this->DBTemp->DB->query($sql);
        $result = $this->DBTemp->DB->get();
        
        $temp=array();
        foreach ($result as $value) {
            if(unserialize($value['dutiesArray'])!=null){
                $temp[$value['staff_id']] = unserialize($value['dutiesArray']);
            } 
        }
        
        foreach ($tempDutyArray as $value) {
            $commitDutyArray[$value['dutyName']]=array();
            foreach ($temp as $key => $valueArray) {
            $tempId = $key;
            foreach ($valueArray as $duty) {
                    if($value['dutyName']==$duty){
                        $commitDutyArray[$value['dutyName']][]=$tempId;
                    }
                }
            }
        }
       // $this->echoArray($commitDutyArray); 
        return $commitDutyArray;
    }
 
    
    function echoArray($array){
        echo"<pre>";
        print_r($array);
    }

}

?>